with  door_sign_tmp1 as
(
 select 
       t.dt as date_time
      ,t.waybill_no
      ,t.agent_code
      ,max(t.agent_name) as agent_name
      ,t.scan_site_code
      ,max(t.scan_site_name) as scan_site_name
      ,max( case when t.pistol_id = 1 then '菜鸟驿站' 
                 when t.pistol_id = 2 then '代收点'  else  tt.business_name end   )  business_name
      ,max(case when t.ordersource_name='七星潭' then 1 else 0 end ) ordersource_flg --订单来源
      ,max(t.pistol_id) pistol_id
 from jms_dm.dm_door_sign_rate_detail_dt t 
 left join (
         select t.waybill_no, max(case when t1.scan_pda <> 'HTKY' and t1.scan_pda <> 'THD20220119001' then t1.business_name   end ) business_name
         from  jms_dwd.dwd_tab_barscan_collect_base_dt t 
         left join jms_dim.dim_tab_terminal_secret_base  t1 on t1.scan_pda = t.pistol_id
         where     t.dt >= '{{ execution_date | date_add(-20) | cst_ds }}' and dt <= '{{ execution_date  | cst_ds }}'
         group by t.waybill_no
          ) tt  on t.waybill_no=tt.waybill_no         
 where t.dt='{{ execution_date  | cst_ds }}'
       and t.is_writeback_flag=1 and pistol_id in(1,2,3)
 group by 
  t.dt
  ,t.waybill_no
  ,t.agent_code
  ,t.scan_site_code
  ,t.pistol_id
)
insert overwrite table jms_dm.dm_door_sign_rate_export_dt partition ( dt )
select 
   t.date_time
  ,t.agent_code
  ,max(t.agent_name) as agent_name
  ,t.scan_site_code as code
  ,max(t.scan_site_name) as name
  ,t.business_name
  ,t.ordersource_flg --订单来源
  ,count(1) as cnt
  ,t.date_time as dt
from door_sign_tmp1 t 
group by 
 t.date_time
,t.agent_code
,t.scan_site_code
,t.business_name
,t.ordersource_flg
 
 distribute by pmod(hash(rand()), 3)
;